-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: insert values duplicate key update,表上带各类约束，部分测试点合理报错

--pre1:切换B库执行
@conn PrimaryDbBmode;

--step1:建表;expect:成功
drop table if exists upsert_hash,upsert_hash_par,upsert_rep;
drop sequence if exists seq2;
create sequence seq2;
create table upsert_hash
(c1 int default nextval('seq2'),
c2 varchar(1024),
c3 text default 'jifjeowjdflaskjdfieourwoejreoiwjfd',
c4 date default current_date,
c5 timestamp default now(),
unique(c1,c4));
create unique index iupsert_hash on upsert_hash(c4,c2);

create table upsert_hash_par
(c1 serial,
c2 text default floor(random()*1000),
c3 varchar(1023) default repeat(random(),100),
c4 date default date '1970-01-10' + random() * (date '2020-01-20 ' - date '1970-01-10'),
c5 timestamp default timestamp '1970-01-10 12:00:00' + random() * (date '2020-01-20 12:00:00' - date '1970-01-10 12:00:00'),
unique(c1,c3))
partition by range(c1)(partition p1 values less than(1000),
partition p2 values less than(maxvalue));
create unique index iupsert_hash_par on upsert_hash_par(c3,c1)local;

create table upsert_rep
(c1 int default nextval('seq2'),
c2 varchar(1024) default repeat(random(),12),
c3 text default repeat('abc',1023),
c4 date default date '1970-01-10' + random() * (date '2020-01-20 ' - date '1970-01-10') primary key,
c5 timestamp default timestamp '1970-01-10 12:00:00' + random() * (date '2020-01-20 12:00:00' - date '1970-01-10 12:00:00'));
create unique index iupsert_rep on upsert_rep(c1);

--step2:执行sql;expect:部分测试点合理报错
insert into upsert_hash values(default)  on duplicate key update c3=values(c2);
insert into upsert_hash(c2,c3) values(repeat('abc',13),default)  on duplicate key update c5=values(c5);
insert into upsert_hash values(1,repeat('abc',13),default,default),
(1,'fjeoir','jfoeiwuroiwe',default)  on duplicate key update c3=excluded.c3;
select count(*) from upsert_hash;
insert into upsert_hash_par select * from upsert_hash on duplicate key update nothing;
insert into upsert_rep select * from upsert_hash on duplicate key update nothing;
insert into upsert_rep select * from upsert_hash order by 1 on duplicate key update c3=nextval('seq2');
insert into upsert_rep(c1) values(default) on duplicate key update c2=default;
alter table upsert_rep drop column c2;
alter table upsert_rep add column c2 text default repeat(random(),10);
insert into upsert_rep(c1,c4) values(20002,'2012-01-27') on duplicate key update c5=timestamp '20200530 12:00:00';

begin;
alter table upsert_hash drop column c3;
alter table upsert_hash add column c3 text;
alter table upsert_hash_par drop column c4;
alter table upsert_hash_par add column c4 date;
insert into upsert_hash_par(c1,c3,c4,c5) select c1,c3,c4,c5 from upsert_hash on duplicate key update nothing;
insert into upsert_hash_par(c1,c3,c4,c5) select c1,c3,c4,c5 from upsert_hash on duplicate key update c2=excluded.c2+100;
/
commit;

select count(*) from upsert_hash_par;
select count(*) from upsert_hash;

--step3:环境清理;expect:成功
drop table upsert_hash cascade;
drop table upsert_hash_par cascade;
drop table upsert_rep cascade;
drop sequence seq2;